package manager.modulo.financeiro.dao;

import java.sql.SQLException;
import manager.auxiliares.MensagemExcessao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.LinkedList;
import manager.auxiliares.Format;
import manager.principal.fonte.JanelaLogin;
import manager.interfaces.InterfaceDao;

/**
 * Esta classe será utilizada para cadastra a entidade user no banco de dados
 * @author Thiago
 */
public class FinancialEntryDescriptionDao implements InterfaceDao {

    int id;
    int value;
    int unavailableValue;
    String description;
    String date;
    Timestamp dateType;
    int situation;

    /**
     * Inicializando as variáveis
     */
    public FinancialEntryDescriptionDao() {
        id = 0;
        value = 0;
        unavailableValue =0;
        description = "";
        date = "";
        dateType = new Timestamp(00 - 00 - 00);
        situation = 0;
    }

    public static LinkedList searchTable(String option, String search) throws SQLException {
        LinkedList result = new LinkedList();
        if (!search.equals("")) {
            String sql = "";
            if (option.equalsIgnoreCase("Nome")) {//Buscando por email
                sql = "SELECT"
                        + "  fc.name as CategoryName,"
                        + "  fc.id   as CategoryId,"
                        + "  fc.id_entry_type as EntryType,"
                        + "  group_concat(sc.name separator ', ') as SecondaryCategoryName"
                        + " FROM"
                        + "  financial_category fc,"
                        + "  secondary_category sc"
                        + " WHERE"
                        + "  fc.name LIKE ? AND"
                        + "  fc.id = sc.id_financial_category"
                        + " GROUP BY"
                        + "  fc.id"
                        + " ORDER BY fc.name;";
                search += "%";
            } else if (option.equalsIgnoreCase("Subscategorias")) {
                sql = "SELECT"
                        + "  fc.name as CategoryName,"
                        + "  fc.id   as CategoryId,"
                        + "  fc.id_entry_type as EntryType,"
                        + "  group_concat(sc.name separator ', ') as SecondaryCategoryName"
                        + " FROM"
                        + "  financial_category fc,"
                        + "  secondary_category sc"
                        + " WHERE"
                        + "  sc.name LIKE ? AND"
                        + "  fc.id = sc.id_financial_category"
                        + " GROUP BY"
                        + "  fc.id"
                        + " ORDER BY fc.name;";
                search += "%";
            } else if (option.equalsIgnoreCase("Tipo de Lançamento")) {
                sql = "SELECT"
                        + "  fc.name as CategoryName,"
                        + "  fc.id   as CategoryId,"
                        + "  fc.id_entry_type as EntryType,"
                        + "  group_concat(sc.name separator ', ') as SecondaryCategoryName"
                        + " FROM"
                        + "  financial_category fc,"
                        + "  secondary_category sc"
                        + " WHERE"
                        + "  fc.id_entry_type = ? AND"
                        + "  fc.id = sc.id_financial_category"
                        + " GROUP BY"
                        + "  fc.id"
                        + " ORDER BY fc.name;";
                search = Integer.toString(EntryTypeDao.getIdEntryTypeByName(search));
            }
            if (!sql.equals("")) {
                PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql);
                preparedStatement.setString(1, search);
                ResultSet resultSet = preparedStatement.executeQuery();

                LinkedList<String> columnName = new LinkedList<String>();
                LinkedList<String> columnSubcategory = new LinkedList<String>();
                LinkedList<String> columnEntryType = new LinkedList<String>();
                LinkedList<Integer> columnId = new LinkedList<Integer>();

                while (resultSet.next()) {
                    columnId.add(resultSet.getInt("CategoryId"));
                    columnName.add(resultSet.getString("CategoryName"));
                    columnEntryType.add(EntryTypeDao.getNameByIdEntryType(resultSet.getInt("EntryType")));
                    columnSubcategory.add(resultSet.getString("SecondaryCategoryName"));
                }

                result.add(columnId);
                result.add(Format.getBooleanLinkedList(columnId.size()));
                result.add(columnName);
                result.add(columnEntryType);
                result.add(columnSubcategory);
                result.add(Format.getBooleanLinkedList(columnId.size()));
            }
        }
        return result;
    }

    public void buscar(int id) {
        try {
            setId(id);
            String sql = "SELECT * FROM financial_entry_descriptionn WHERE id = ?";
            PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.first()) {
                value = resultSet.getInt("value");
                unavailableValue = resultSet.getInt("unavailablevalue");
                description = resultSet.getString("description");
                dateType = resultSet.getTimestamp("date");
                date = Format.formatarTimeStampApresentacao(dateType);
                situation = resultSet.getInt("situation");
            }
            resultSet.close();
            preparedStatement.close();
        } catch (Exception e) {
            MensagemExcessao.mostrarMensagem(e, "Buscando Lançamentos");
        }
    }

    public void inserir() throws SQLException {
        String sql = "INSERT INTO financial_entry (value,description,date,situation,unavailablevalue) VALUES(?,?,?,?,?)";
        PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

        preparedStatement.setInt(1, value);
        preparedStatement.setString(2, description);
        preparedStatement.setTimestamp(3, dateType);
        preparedStatement.setInt(4, situation);
        preparedStatement.setInt(5, unavailableValue);
        preparedStatement.executeUpdate();

        //Recebendo o novo id gerado
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        while (resultSet.next()) {
            id = resultSet.getInt(1);
        }

        preparedStatement.close();
        resultSet.close();
    }

    public void atualizar() throws SQLException {
        String sql = "UPDATE financial_entry_description SET value= ?, description = ?, date = ?, situation = ?, unavailablevalue = ? WHERE id = ?";
        PreparedStatement preparedStatement = JanelaLogin.getConnection().prepareStatement(sql);
        preparedStatement.setInt(1, value);
        preparedStatement.setString(2, description);
        preparedStatement.setTimestamp(3, dateType);
        preparedStatement.setInt(4, situation);
        preparedStatement.setInt(5, unavailableValue);
        preparedStatement.setInt(6, id);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }

    public void deletar() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public static LinkedList<String> getListSituation() {
        LinkedList<String> entrySituations = new LinkedList<String>();
        entrySituations.add("HABILITADA");
        entrySituations.add("CANCELADA");

        return entrySituations;
    }

    public static String getSituation(String type, int situation) {
        if (type.equalsIgnoreCase("DESPESA")) {
            if (situation == 0) {
                return "NÃO PAGO";
            } else {
                return "PAGO";
            }
        } else if (type.equalsIgnoreCase("RECEITA")) {
            if (situation == 0) {
                return "NÃO RECEBIDO";
            } else {
                return "RECEBIDO";
            }
        } else {
            if (situation == 0) {
                return "NÃO MOVIMENTADO";
            } else {
                return "MOVIMENTADO";
            }
        }
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public Timestamp getDateType() {
        return dateType;
    }

    public void setDateType(Timestamp dateType) {
        this.dateType = dateType;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getSituation() {
        return situation;
    }

    public void setSituation(int situation) {
        this.situation = situation;
    }

    public int getValue() {
        return value;
    }

    public void setValue(int value) {
        this.value = value;
    }

    public LinkedList<String> buscar(String opcao, String buscado, boolean limite) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
